const express = require('express');
const { executeQuery, handleError, formatDateTime } = require('../utils'); // 引入 formatDateTime
const router = express.Router();

// 新增委托
router.post('/add', async (req, res) => {
    const { user_id, journal_id, from_location_id, to_location_id } = req.body;

    const checkQuery = `
        SELECT COUNT(*) AS count 
        FROM entrustments 
        WHERE user_id = ? AND journal_id = ? AND status = 'pending';
    `;

    const insertQuery = `
        INSERT INTO entrustments (user_id, journal_id, from_location_id, to_location_id, status, created_at)
        VALUES (?, ?, ?, ?, 'pending', NOW());
    `;

    try {
        const checkResult = await executeQuery(checkQuery, [user_id, journal_id]);
        if (checkResult[0].count > 0) {
            return res.status(400).json({ message: '该委托已存在，无法重复提交！' });
        }

        await executeQuery(insertQuery, [user_id, journal_id, from_location_id, to_location_id]);
        res.json({ message: '委托申请成功！' });
    } catch (error) {
        handleError(error, res);
    }
});

// 查询委托
router.post('/list', async (req, res) => {
    const { page = 1, pageSize = 10, search = '', user_id } = req.body;
    const offset = (page - 1) * pageSize;
    const limit = pageSize;

    const searchQuery = `%${search}%`;

    let totalQuery = `
        SELECT COUNT(*) AS total
        FROM entrustments e
        JOIN users u ON e.user_id = u.user_id
        JOIN journals j ON e.journal_id = j.journal_id
        JOIN journaldirectories d ON j.directory_id = d.directory_id
        WHERE (u.name LIKE ? OR d.journal_name LIKE ?)`;

    let query = `
        SELECT 
            e.entrustment_id, 
            u.name AS user_name, 
            d.journal_name, 
            l1.location_name AS from_location, 
            l2.location_name AS to_location, 
            e.status, 
            e.created_at
        FROM entrustments e
        JOIN users u ON e.user_id = u.user_id
        JOIN journals j ON e.journal_id = j.journal_id
        JOIN journaldirectories d ON j.directory_id = d.directory_id
        JOIN librarylocation l1 ON e.from_location_id = l1.location_id
        JOIN librarylocation l2 ON e.to_location_id = l2.location_id
        WHERE (u.name LIKE ? OR d.journal_name LIKE ?)`;

    const queryParams = [searchQuery, searchQuery];

    if (user_id) {
        totalQuery += ` AND e.user_id = ?`;
        query += ` AND e.user_id = ?`;
        queryParams.push(user_id);
    }

    totalQuery += `;`;
    query += ` LIMIT ${limit} OFFSET ${offset};`;

    try {
        const totalResult = await executeQuery(totalQuery, queryParams);
        const total = totalResult[0]?.total || 0;

        if (total === 0) {
            return res.json({ total, page, pageSize, data: [] });
        }

        const records = await executeQuery(query, queryParams);

        // 格式化时间字段
        const formattedRecords = records.map(record => ({
            ...record,
            created_at: formatDateTime(record.created_at),
        }));

        res.json({ total, page, pageSize, data: formattedRecords });
    } catch (error) {
        console.error('查询委托记录失败:', error);
        res.status(500).json({ message: '查询失败', error: error.sqlMessage || error.message });
    }
});

// 更新委托状态
router.post('/update-status', async (req, res) => {
    const { entrustment_id, status } = req.body;

    const updateQuery = `
        UPDATE entrustments
        SET status = ?, updated_at = NOW()
        WHERE entrustment_id = ?;
    `;

    const adjustStockQuery = `
        UPDATE journals
        SET stock = stock + ?
        WHERE directory_id = ? AND location_id = ?;
    `;

    const selectEntrustmentQuery = `
        SELECT journal_id, from_location_id, to_location_id, user_id
        FROM entrustments
        WHERE entrustment_id = ?;
    `;

    const checkJournalInLocationQuery = `
        SELECT COUNT(*) AS count
        FROM journals
        WHERE directory_id = ? AND location_id = ?;
    `;

    const insertJournalInLocationQuery = `
        INSERT INTO journals (year, volume, issue, directory_id, location_id, stock, created_at, updated_at)
        SELECT 
            year, 
            volume, 
            issue, 
            directory_id, 
            ?, 
            1, 
            NOW(), 
            NOW()
        FROM journals
        WHERE journal_id = ? AND location_id = ?;
    `;

    const insertBorrowRecordQuery = `
        INSERT INTO borrowrecords (user_id, journal_id, borrow_date, return_date, status)
        VALUES (?, ?, NOW(), DATE_ADD(NOW(), INTERVAL 1 WEEK), 'locked');
    `;

    try {
        // 如果状态为 `approved`，更新库存并检查目标馆藏地
        if (status === 'approved') {
            const entrustment = await executeQuery(selectEntrustmentQuery, [entrustment_id]);

            if (entrustment.length > 0) {
                const { journal_id, from_location_id, to_location_id, user_id } = entrustment[0];

                // 获取期刊目录 ID
                const journalDetails = await executeQuery(
                    `SELECT directory_id FROM journals WHERE journal_id = ?;`,
                    [journal_id]
                );
                const directory_id = journalDetails[0]?.directory_id;

                if (!directory_id) {
                    return res.status(400).json({ message: '无效的期刊目录 ID！' });
                }

                // 检查目标馆藏地是否有该期刊记录
                const checkResult = await executeQuery(checkJournalInLocationQuery, [directory_id, to_location_id]);

                if (checkResult[0].count === 0) {
                    // 如果目标馆藏地没有该期刊记录，则新增一条记录并设置库存为1
                    await executeQuery(insertJournalInLocationQuery, [to_location_id, journal_id, from_location_id]);
                } else {
                    // 如果目标馆藏地已有记录，直接增加库存
                    await executeQuery(adjustStockQuery, [1, directory_id, to_location_id]);
                }

                // 减少原馆藏地库存
                await executeQuery(adjustStockQuery, [-1, directory_id, from_location_id]);

                // 自动生成借阅记录
                await executeQuery(insertBorrowRecordQuery, [user_id, journal_id]);

            } else {
                return res.status(400).json({ message: '没有找到对应的委托记录！' });
            }
        }

        // 更新委托状态
        await executeQuery(updateQuery, [status, entrustment_id]);
        res.json({ message: '委托状态更新成功，借阅记录已生成！' });
    } catch (error) {
        console.error('更新委托状态失败:', error);
        handleError(error, res);
    }
});

// 删除委托
router.post('/delete', async (req, res) => {
    const { entrustment_id } = req.body;

    const deleteQuery = `
        DELETE FROM entrustments
        WHERE entrustment_id = ?;
    `;

    try {
        await executeQuery(deleteQuery, [entrustment_id]);
        res.json({ message: '委托记录删除成功！' });
    } catch (error) {
        handleError(error, res);
    }
});

module.exports = router;
